package org.auf.model.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Date;

import org.apache.log4j.Logger;
import org.auf.model.dao.IPairedDAO;
import org.auf.to.Paired;
import org.auf.to.User;

public class PairedDAOJDBC implements IPairedDAO {
	
	static Logger log = Logger.getLogger(PairedDAOJDBC.class.getName());
	
	private static PairedDAOJDBC INSTANCE = null;
    
    private PairedDAOJDBC() {}
    
    private synchronized static void createInstance() {
        if (INSTANCE == null) { 
            INSTANCE = new PairedDAOJDBC();
        }
    }
 
    public static IPairedDAO getInstance() {
        if (INSTANCE == null) createInstance();
        return INSTANCE;
    }

	@Override
	public boolean insertPaired(Paired paired) {
		int updates=0;
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("INSERT INTO tblpaired (id,volunteer,geek,date,testCompleted) values (?,?,?,?,?)");
			st.setString(1, paired.getId());
			st.setString(2, paired.getVolunteer().getId());
			st.setString(3, paired.getGeek().getId());
			st.setDate(4, new java.sql.Date(paired.getDate().getTime()));
			st.setBoolean(5, paired.getTestCompleted());
			updates =st.executeUpdate();
		
		}catch(Exception e){updates=-1;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return updates==1;
	}

	@Override
	public Paired selectLastPairedByGeek(User user) {
		Paired paired=new Paired();

		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("SELECT * FROM tblpaired WHERE geek=? ORDER BY date DESC LIMIT 0,1");
			st.setString(1, user.getId());
			ResultSet rs = st.executeQuery();
		
			if (rs.next()){
				paired.setId(rs.getString("id"));
				paired.setVolunteer(UserDAOJDBC.getInstance().selectUserByid(rs.getString("volunteer")));
				paired.setGeek(UserDAOJDBC.getInstance().selectUserByid(rs.getString("geek")));
				paired.setDate(rs.getDate("date"));
				paired.setTestCompleted(rs.getBoolean("testCompleted"));
				
			}else paired=null;
		
		}catch(Exception e){paired=null;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return paired;
	}

	@Override
	public boolean updatePairedTestCompleted(Paired paired) {
		int updates=0;
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("UPDATE tblpaired SET testCompleted = ? WHERE id = ?");
			st.setBoolean(1, paired.getTestCompleted());
			st.setString(2, paired.getId());
			updates =st.executeUpdate();
		
		}catch(Exception e){updates=-1;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return updates==1;
	}

}
